﻿
import MySQLdb

class db_ebook:
	def connect_db(self, db_name = 'itebook.db'):
		conn = MySQLdb.connect(host = 'localhost', user = 'root', passwd = 'psd-9898w', db = 'itebooks')
		self.conn = conn
		cursor = conn.cursor()
		cursor.execute("SET NAMES utf8")
		cursor.execute("SET CHARACTER_SET_CLIENT=utf8")
		cursor.execute("SET CHARACTER_SET_RESULTS=utf8")
		cursor.execute('SET AUTOCOMMIT = 1')
		
	def execute_sql(self, sql, param = None):
		if not self.conn:
			print "error, cannot connect to db"
		#	return#不返回，直接让它抛异常
		if not self.conn.cursor:
			print "can not get cursor"
		cursor = self.conn.cursor
		if param:
			cursor.execute(sql, param)
		else:
			cursor.execute(sql)
		return cursor
		
	def execute_scaler(self, sql, param = None):
		cursor = self.execute_sql(sql, param)
		row = cursor.fetchone()
		return row[0]
		
	def create_tb(self):
		sql = '''create table tb_posts(
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_author bigint(20) unsigned NOT NULL DEFAULT 0,
			post_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			post_date_get datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			post_source bigint(20) unsigned NOT NULL DEFAULT 0,/*来源*/
			post_title text NOT NULL DEFAULT '',
			post_abstract text NOT NULL DEFAULT '',
			post_content text NOT NULL DEFAULT '',
			post_status bigint(20) unsigned NOT NULL DEFAULT 0,/*是否可见*/
			post_ping bigint(20) unsigned NOT NULL DEFAULT 0,
			post_comment bigint(20) NOT NULL DEFAULT 0,
			post_mark bigint(20) DEFAULT 0,/*评分*/
			post_tags varchar(20) NOT NULL DEFAULT '', /*以逗号分割*/
			post_category varchar(10) NOT NULL DEFAULT '', /*以逗号分割*/
			primary key(ID)
		);'''
		self.execute_sql(sql)
		sql = '''create table tb_tags(
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			tag_name varchar(32) NOT NULL DEFAULT '',
			primary key(ID)
		);'''
		self.execute_sql(sql)
		sql = '''create table tb_tags(
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			tag_name varchar(32) NOT NULL DEFAULT '',
			primary key(ID)
		);'''
		self.execute_sql(sql)
		sql='''CREATE TABLE tb_author (
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			`src_url` varchar(1024) NOT NULL DEFAULT '',
			`name` varchar(256) NOT NULL DEFAULT '',
			PRIMARY KEY (`id`)
		);'''
		self.execute_sql(sql)
		sql='''CREATE TABLE tb_source (
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			`src_url` varchar(1024) NOT NULL DEFAULT '',
			`name` varchar(256) NOT NULL DEFAULT '',
			PRIMARY KEY (`id`)
		);'''
		self.execute_sql(sql)
		sql='''CREATE TABLE tb_tag (
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			`src_url` varchar(1024) NOT NULL DEFAULT '',
			`name` varchar(256) NOT NULL DEFAULT '',
			PRIMARY KEY (`id`)
		);'''
		self.execute_sql(sql)
		sql='''CREATE TABLE tb_category (
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			`src_url` varchar(1024) NOT NULL DEFAULT '',
			`name` varchar(256) NOT NULL DEFAULT '',
			PRIMARY KEY (`id`)
		);'''
		self.execute_sql(sql)
		sql='''CREATE TABLE tb_status (/*8421的这样子*/
			ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			`src_url` varchar(1024) NOT NULL DEFAULT '',
			`name` varchar(256) NOT NULL DEFAULT '',
			PRIMARY KEY (`id`)
		);'''
		self.execute_sql(sql)

	def add_author(self, src_url, name):
		cursor = self.conn.cursor()
		sql = '''insert into author(src_url, name) value(%s, %s);'''
		cursor.execute(sql, (src_url, name))

	def is_author_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from author where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def add_tag(self, src_url, name):
		cursor = self.conn.cursor()
		sql = '''insert into tag(src_url, name) value(%s, %s);'''
		cursor.execute(sql, (src_url, name))

	def is_tag_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from tag where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def add_publisher(self, src_url, name):
		cursor = self.conn.cursor()
		sql = '''insert into publisher(src_url, name) value(%s, %s);'''
		cursor.execute(sql, (src_url, name))

	def is_publisher_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from publisher where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def book_click(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_click(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def book_buy(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_buy(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def book_download(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_download(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def book_read(self, bookid, userip):
		cursor = self.conn.cursor()
		sql = '''insert into book_read(bookid, userip) value(%s, %s);'''
		cursor.execute(sql, (bookid, userip))

	def is_book_exists(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select count(0) from book where src_url=%s;''' #% src_url
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0] > 0

	def get_book_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from book where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def get_tag_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from tag where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def get_author_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from author where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def get_publisher_id(self, src_url):
		cursor = self.conn.cursor()
		sql = '''select id from publisher where src_url=%s;'''
		cursor.execute(sql, (src_url, ))
		row = cursor.fetchone()
		return row[0]

	def add_book(self, src_url, title, title1, img_url, isbn, datepublished, pages, language, file_size, file_format, down_url, buy_url, read_url, description):
		cursor = self.conn.cursor()
		sql = '''insert into book(src_url, title, title1, img_url, isbn, datepublished, pages, language, file_size, file_format, down_url, buy_url, read_url, description) value(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'''
		cursor.execute(sql, (src_url, title, title1, img_url, isbn, str(datepublished), str(pages), language, file_size, file_format, down_url, buy_url, read_url, description))

	def add_book_author(self, bookid_authorids):
		cursor = self.conn.cursor()
		sql = '''insert into book_author(bookid, authorid) values (%s, %s);'''
		cursor.executemany(sql, list(bookid_authorids))

	def add_book_publisher(self, bookid_publisherids):
		cursor = self.conn.cursor()
		sql = '''insert into book_publisher(bookid, publisherid) values (%s, %s);'''
		cursor.executemany(sql, list(bookid_publisherids))

	def add_book_tag(self, bookid_tagids):
		cursor = self.conn.cursor()
		sql = '''insert into book_tag(bookid, tagid) values (%s, %s);'''
		cursor.executemany(sql, list(bookid_tagids))

if __name__ == '__main__':
	print '你好, world'
